package models

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"errors"
	"strings"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/lib/pq"
	"gopkg.in/guregu/null.v4"
	"learning.com/edu/utils/helper"
)

var (
	VerifyCodeTypeEmail = "email"
	VerifyCodeTypeSMS = "sms"
)

var (
	VerifyCodeScopeLogin = "email"
	VerifyCodeScopeResetPassword = "reset_password"
	VerifyCodeScopeResetEmail = "reset_email"
	VerifyCodeScopeResetPhoneNumber = "reset_phonenumber"
)

// 用户
type User struct {
	ID   int64 `json:"id"`
	Type string `json:"type"`// 用户类型
}

var AnonymousUser = &User{}
func (u *User) IsAnonymous() bool {
	return u == AnonymousUser
}

// 用户简略信息
type UserBrief struct {
	User
	Name 			  string 		`json:"name" db:"name"`// 用户名
	Gender 			  null.String   `json:"gender,omitempty" db:"gender"`// 性别
	Avatar 			  null.String   `json:"avatar,omitempty" db:"avatar"`// 头像
	PasswordHash 	  []byte 		`json:"-" db:"password_hash"`// 密码哈希
	CreatedAt 		  time.Time 	`json:"created_at" db:"created_at"`// 创建时间
	PurchasingCourses pq.Int64Array `json:"purchasing_courses" db:"purchasing_courses"`// 购物车中要购买的课程
	PurchasedCourses  pq.Int64Array `json:"purchased_courses" db:"purchased_courses"`// 已购买的课程
}

// 用户信息
type UserProfile struct { 
	UserBrief
	Email 			null.String    `json:"email,omitempty" db:"email"` // 邮箱
	PhoneNumber 	null.String    `json:"phone_number,omitempty" db:"phone_number"`// 手机号
	WechatId 		null.String    `json:"wechat_id,omitempty" db:"wechat_id"` // 微信ID
	Region 			null.String    `json:"region,omitempty" db:"region"` // 地区
	RegionCodeArray pq.StringArray `json:"region_code_array,omitempty" db:"region_code_array"` // 地区代码
	Introduction 	null.String    `json:"introduction,omitempty" db:"introduction"`// 介绍
	Profession 		null.String    `json:"profession,omitempty" db:"profession"` // 职业
	UpdatedAt 		time.Time      `json:"updated_at" db:"updated_at"` // 更新时间
}

type UserModel struct {
	DB 	   *sqlx.DB
	helper *helper.Helper
}

/**
 * @description: 根据用户 ID 查询用户详细信息
 * @param {int64} id
 * @return {*}
 * 1.从business schema 下的shopping_carts表中,
 * 筛选出user_id等于u.id的所有记录,然后将这些记录中的course_id值聚合为一个数组并返回
 * 2.从business模式下的orders表中，筛选出所有user_id等于u.id(通常是关联查询中另一张表的用户 ID)的记录，
 * 然后将这些记录中的course_id值聚合到一个数组中
 */
func (u *UserModel) GetUserProfileById(id int64) (*UserProfile, error) {
	query := `
		SELECT
			u.id as id, u.name as name, u.email as email, 
			u.phone_number as phone_number,
			u.wechat_id as wechat_id, u.type as type, u.gender as gender,
			region_query.region_name as region, 
			region_query.region_code_array as region_code_array,
			u.avatar as avatar, u.introduction as introduction, p.title as profession,
			u.created_at as created_at, u.updated_at as updated_at, 
			u.password_hash as password_hash,
			(
				SELECT 
					ARRAY_AGG(course_id) 
				FROM 
					business.shopping_carts
				WHERE 
					user_id = u.id
			) AS purchasing_courses,
			(
				SELECT 
					ARRAY_AGG(course_id) 
				FROM 
					business.orders
				WHERE 
					user_id = u.id
			) AS purchased_courses
		FROM 
			appuser.users u
		LEFT JOIN 
			appuser.professions p ON u.profession_id = p.id
		LEFT JOIN LATERAL 
			china_administrative.get_region_query(u.region) as region_query ON TRUE
		WHERE 
			u.id = $1`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var user UserProfile
	err := u.DB.QueryRowxContext(ctx, query, id).StructScan(&user)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, helper.ErrRecordNotFound
		}
		return nil, err
	}
	return &user, nil
}

/**
 * 通过账号（account）或 ID 查询用户简要信息,支持通过邮箱、手机号或 ID 查询
 * @param account 账号
 * @param id ID
 * @return 用户简要信息
 * @throws RecordNotFoundError
 * 子查询1:从购物车表business.shopping_cart聚合用户的待购课程ID,聚合为数组purchasing_courses
 * 子查询2:从订单表business.orders聚合用户的已购课程ID,聚合为数组purchased_courses
 */
 func (u *UserModel) GetUserBriefByAcountOrID(account string, id int64) (*UserBrief, 
	error) { 
	query := `
		SELECT
			id, name, type, gender, avatar, password_hash, created_at,
			(
				SELECT ARRAY_AGG(course_id) FROM business.shopping_carts
				WHERE user_id = users.id
			) AS purchasing_courses,
			(
				SELECT ARRAY_AGG(course_id) FROM business.orders
				WHERE user_id = users.id
			) AS purchased_courses
		FROM appuser.users
		WHERE email = $1 OR phone_number = $1 OR id = $2`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var user UserBrief
	err := u.DB.QueryRowxContext(ctx, query, account, id).StructScan(&user)
	if err != nil {
		switch {
			case errors.Is(err, sql.ErrNoRows):
				return nil, helper.ErrRecordNotFound
			default:
				return nil, err
		}
	}
	return &user, nil
}

/**
 * 通过token获取用户简略信息
 */
func (u *UserModel) GetUserForToken(tokenScope, token string) (*User, error) { 
	tokenHash := sha256.Sum256([]byte(token))
	query := `
		SELECT 
			u.id AS id,
			u.type AS type
		FROM 
			appuser.users u
		INNER JOIN 
			appuser.tokens t 
		ON 
			u.id = t.user_id
		WHERE 
			t.hash = $1
		AND 
			t.scope = $2
		AND 
			t.expiry > $3`
	
	args := []any {
		tokenHash[:],
		tokenScope,
		time.Now(),
	}

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var user User
	err := u.DB.QueryRowxContext(ctx, query, args...).StructScan(&user)
	if err != nil {
		switch {
		case errors.Is(err, sql.ErrNoRows):
			return nil, helper.ErrRecordNotFound
		default:
			return nil, err
		}
	}
	return &user, nil
}

/**
 * 获取用户头像
 */
func (u *UserModel) GetUserAvatarById(id int64) (*string, error) {
	query := `SELECT avatar FROM appuser.users WHERE id = $1`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var avatar null.String
	err := u.DB.QueryRowxContext(ctx, query, id).Scan(&avatar)
	if err != nil {
		return nil, err
	}
	
	// 存在头像
	if avatar.Valid {
		return &avatar.String, nil
	} else {
		return nil, nil
	}
}

/**
 * 修改用户头像
 */
func (u *UserModel) UpdateUserAvatar(id int64, avatar string) (string, error) {
	query := `
		WITH 
			old_values 
		AS 
			(SELECT avatar FROM appuser.users WHERE id = $1)
		UPDATE
			appuser.users
		SET
			avatar = $2
		WHERE 
			id = $1
		RETURNING 
			(SELECT avatar FROM old_values)`

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var oldAvatar sql.NullString
	err := u.DB.QueryRowxContext(ctx, query, id, avatar).Scan(&oldAvatar)
	if err != nil {
		return "", err
	} else {
		return oldAvatar.String, nil
	}
}

/**
 * 更新用户昵称
 */
func (u *UserModel) UpdateUserNickName(id int64, nickName string) error {
	query := `UPDATE appuser.users SET name = $1 WHERE id = $2`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := u.DB.ExecContext(ctx, query, nickName, id)
	return err
}

/**
 * 更新用户职业
 */
func (u *UserModel) UpdateUserProfession(id int64, professionId int64) (string, error) {
	query := `
		UPDATE 
			appuser.users 
		SET 
			profession_id = $1 
		WHERE 
			id = $2
		RETURNING
			(SELECT title FROM appuser.professions WHERE id = $1)`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var value sql.NullString
	err := u.DB.QueryRowContext(ctx, query, professionId, id).Scan(&value)
	if err != nil {
		return "", err
	}
	return value.String, nil
}

/**
 * 更新用户性别
 */
func (u *UserModel) UpdateUserGender(id int64, gender string) error {
	query := `UPDATE appuser.users SET gender = $1 WHERE id = $2`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := u.DB.ExecContext(ctx, query, gender, id)
	return err
}

/**
 * 更新用户所在地址或地区
 */
func (u *UserModel) UpdateUserRegion(id int64, code int64) (string, []string, error) {
	query := `UPDATE appuser.users SET region = $1 WHERE id = $2`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := u.DB.ExecContext(ctx, query, code, id)
	if err != nil {
		return "", nil, err
	}

	var region string
	var regionCode []string
	sql := `
		SELECT 
			(region_query).region_name, (region_query).region_code_array
		FROM
			(SELECT china_administrative.get_region_query($1) AS region_query)
		AS
			subquery`
	err = u.DB.QueryRowxContext(ctx, sql, code).Scan(&region, pq.Array(&regionCode))
	if err != nil {
		return "", nil, err
	}
	return region, regionCode, nil
}

/**
 * 更新用户签名
 */
func (u *UserModel) UpdateUserIntroduction(id int64, introduction string) error {
	query := `UPDATE appuser.users SET introduction = $1 WHERE id = $2`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := u.DB.ExecContext(ctx, query, introduction, id)
	return err
}

/**
 * 获取所有职业信息
 */
type Profession struct {
	ID   int64  `json:"id" db:"id"`
	Title string `json:"title" db:"title"`
}
func (u *UserModel) GetAllProfessions() ([]*Profession, error) {
	query := `SELECT id, title FROM appuser.professions ORDER BY id ASC`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	rows, err := u.DB.QueryxContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	professions := []*Profession{}
	for rows.Next() {
		var p Profession
		err := rows.StructScan(&p)
		if err != nil {
			return nil, err
		}
		professions = append(professions, &p)
	}

	return professions, nil
}

type ChinaRegion struct {
	Code     string   `json:"code" db:"code"`
	Name     string   `json:"name" db:"name"`
	Level    int64    `json:"level" db:"level"`
	Category int64    `json:"category" db:"category"`
}

func (m *UserModel) GetChinaRegionByCode(code int64) ([]*ChinaRegion, error) {
	var query string
	var args []any
	if(code == 0) {
		query = `
			SELECT 
				code, name, level, category 
			FROM 
				china_administrative.regions 
			WHERE 
				pcode is NULL 
			ORDER BY 
				code`
		args = []any{}
	} else {
		query = `
			SELECT 
				code, name, level, category 
			FROM 
				china_administrative.regions 
			WHERE 
				pcode = $1 
			ORDER BY 
				code`
		args = []any{code}
	}

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	rows, err := m.DB.QueryxContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	areas := []*ChinaRegion{}
	for rows.Next() {
		var area ChinaRegion
		if err := rows.StructScan(&area); err != nil {
			return nil, err
		}
		areas = append(areas, &area)
	}
	return areas, nil
}

/**
 * 注册
 */
func (m *UserModel) RegisterUser(account, accountType string) (*UserBrief, error) {
	var sql string
	if accountType == "email" {
		sql = `
			INSERT INTO appuser.users (name, email) VALUES ('Learner', $1)
			RETURNING id, type, name, gender, avatar, password_hash, created_at`
	} else {
		sql = `
			INSERT INTO appuser.users (name, phone_number) VALUES ('Learner', $1)
			RETURNING id, type, name, gender, avatar, password_hash, created_at`
	}

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var user UserBrief
	err := m.DB.QueryRowxContext(ctx, sql, account).StructScan(&user)
	if err != nil {
		return nil, err
	}

	return &user, nil
}

/**
 * 验证码验证
 */
func (m *UserModel) VerifyCode(code, codeType, scope, holdValue string) (bool, error) {
	query := `
		WITH valid_code AS (
			SELECT 1 FROM appuser.verificodes
			WHERE code = $1 AND type = $2
			AND scope = $3 AND hold_value = $4
			AND expiry > NOW()
		),
		delet_code AS (
			DELETE FROM appuser.verificodes
			WHERE code = $1 AND type = $2
			AND scope = $3 AND hold_value = $4
			RETURNING 1
		)
		SELECT
			EXISTS(SELECT 1 FROM valid_code) as valid,
			EXISTS(SELECT 1 FROM delet_code) as deleted`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var result struct {
		Valid  bool  `db:"valid"`
		Deleted  bool `db:"deleted"`
	}

	args := []any{code, codeType, scope, holdValue}
	err := m.DB.QueryRowxContext(ctx, query, args...).StructScan(&result)
	if err != nil {
		return false, err
	}

	return result.Valid, nil
}

/**
 * 通过手机号或邮箱判断账号是否存在
 */
func (m *UserModel) GetAccountIdIfExists(account string) (*int64, error) {
    query := `SELECT id FROM appuser.users WHERE email = $1 OR phone_number = $1 LIMIT 1`
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    var id int64
    err := m.DB.QueryRowContext(ctx, query, account).Scan(&id)
    if err != nil {
        if err == sql.ErrNoRows {
            // No matching account found
            return nil, nil
        }
        // An error occurred
        return nil, err
    }
    return &id, nil
}

/**
 * 生成验证码
 */
func (m *UserModel) GenerateVerifyCode(userId int64, codeType, scope, holdValue string, duration time.Duration) (string, error) {
	// Generate a random 6-digit verification code.
	code := m.helper.RandomString(6)
	code = strings.ToUpper(code)

	// Set up the SQL query.
	query := `
		INSERT INTO 
			appuser.verificodes (code, user_id, type, scope, hold_value, expiry)
		VALUES 
			($1, $2, $3, $4, $5, $6)`

	// Execute the query, passing the code, user ID, and expiry time as the query arguments.
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var userIdInterface interface{}
	if userId == 0 {
		userIdInterface = nil
	} else {
		userIdInterface = userId
	}
	args := []any{code, userIdInterface, codeType, scope, holdValue, time.Now().Add(duration)}

	_, err := m.DB.ExecContext(ctx, query, args...)
	if err != nil {
		return "", err
	}

	return code, nil
}

/**
 * 判断邮箱是否存在
 */
func (m *UserModel) IsEmailExists(email string) (bool, error) {
	query := `SELECT EXISTS(SELECT 1 FROM appuser.users WHERE email = $1)`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var exists bool
	err := m.DB.QueryRowContext(ctx, query, email).Scan(&exists)
	if err != nil {
		return false, err
	}
	return exists, nil
}

/**
 * 更新邮箱
 */
 func (m *UserModel) UpdateUserEmail(id int64, email string) error {
	query := `UPDATE appuser.users SET email = $1 WHERE id = $2`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := m.DB.ExecContext(ctx, query, email, id)
	return err
}

/**
 * 判断手机号是否存在
 */
 func (m *UserModel) IsPhoneNumberExists(email string) (bool, error) {
	query := ` SELECT EXISTS(SELECT 1 FROM appuser.users WHERE phone_number = $1)`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var exists bool
	err := m.DB.QueryRowContext(ctx, query, email).Scan(&exists)
	if err != nil {
		return false, err
	}
	return exists, nil
}

/**
 * 更新手机号
 */
 func (m *UserModel) UpdateUserPhoneNumber(id int64, phoneNumber string) error {
	query := `UPDATE appuser.users SET phone_number = $1 WHERE id = $2`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := m.DB.ExecContext(ctx, query, phoneNumber, id)
	return err
}

/**
 * 更新用户密码
 */
 func (m *UserModel) UpdateUserPassword(id int64, newPasswordHash []byte) error {
	query := `UPDATE appuser.users SET password_hash = $1 WHERE id = $2`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := m.DB.ExecContext(ctx, query, newPasswordHash, id)
	return err
}
